package com.yyzzzz.poi.service;

import com.github.crab2died.ExcelUtils;

import com.yyzzzz.poi.domain.Video;


import java.sql.*;
import java.util.List;

/**
 * Created by yyzzzz on 2018/12/7.
 */
public class VideoService {

    public static void main(String[] args) {
        String path = "C:\\Users\\yyzzzz\\Desktop\\12-09.xlsx";
        try {

            // 1)
            // 不基于注解,将Excel内容读至List<List<String>>对象内
//            List<List<String>> lists = ExcelUtils.getInstance().readExcel2List(path, 1, 0, 0);
//            System.out.println("读取Excel至String数组：");
//            for (List<String> list : lists) {
//                System.out.println(list);
//            }

            // 2)
            // 基于注解,将Excel内容读至List<Student2>对象内
            // 验证读取转换函数Student2ExpelConverter
            // 注解 `@ExcelField(title = "是否开除", order = 5, readConverter =  Student2ExpelConverter.class)`
            List<Video> videos = ExcelUtils.getInstance().readExcel2Objects(path, Video.class, 0, 0);
            System.out.println("读取Excel至对象数组(支持类型转换)：");
            for (Video video : videos) {
                System.out.println(video);
                insertVideo(video);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void insertVideo(Video video) {

        //连接对象
        Connection conn = null;
        //SQL语句执行对象
        PreparedStatement ps = null;
        Statement statement = null;
        // 加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //建立连接,localhost为主机IP地址（本机），3306为mysql的端口号，
            //testdb为数据库的库名,characterEncoding=utf-8为字节编码集
            //root为mysql的登录名，123456为mysql的登录密码
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/poi_test?characterEncoding=utf-8&useSSL=false",
                    "root", "1230");
            //查询关联表信息
            statement = conn.createStatement();
            String sql = "select id from school where name = '"+video.getShopSign()+"'";
            ResultSet resultSet = statement.executeQuery(sql);
            int id = 0; //未找到对应的org 数据
            if (resultSet.next()){
                id = resultSet.getInt(1);
                System.out.println(id);
            }
            //执行SQL语句，？为占位符
            ps = conn.prepareStatement("insert into video(url,video_index,video_name,org_id) values(?,?,?,?)");
            //填充占位符
            for (int i = 0; i < video.getVideoNum(); i++) {
//                ps.setString(1, "http://232.11.22.11/cammear/" + video.getVideoCode() + "$" + i + "/asdasaddsad");
                ps.setString(1, "http://218.4.142.157:83/pag/192.168.100.241/7302/00" + (i + Integer.valueOf(video.getVideoCode())) + "/0/MAIN/TCP/live.m3u8");
                ps.setInt(2, i);
                ps.setString(3, video.getShopSign() + "_" + (i + 1));
                ps.setInt(4, id);
                ps.addBatch();
            }
            //更新数据库
            ps.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {//关闭连接
            try {
                ps.close();
                statement.close();
                conn.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}
